package com.ruoyi.web.spireExcel;

import com.google.common.primitives.Ints;
import com.spire.xls.*;
import com.spire.xls.collections.AutoFiltersCollection;
import lombok.extern.slf4j.Slf4j;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Slf4j
public class ExcelFilter {

    private String directory;

    private String directoryOfFilted;

    public ExcelFilter(String directory, String directoryOfFilted) {
        this.directory = directory;
        this.directoryOfFilted = directoryOfFilted;
    }

    public String filt(String fileName, List<String> numbers){
        //加载Excel示例文档
        long startTime=System.currentTimeMillis();
        Workbook wb = new Workbook();
        wb.loadFromFile(directory+fileName);

        //获取第一个工作表
        Worksheet sheet = wb.getWorksheets().get(0);

        //获取首行
        //找到筛选列
        int index=0;
        for (int i = 1; i <= sheet.getLastColumn(); i++) {
            String[] s={"渠道编码","受理局向","渠道编号","代理商编码","渠道"};
            List<String> names= Arrays.asList(s);
           boolean isNum= names.contains(sheet.get(1,i).getValue());
           if(isNum){
               index=i;
               break;
           }
        }

        //获取AutoFiltersCollection对象
        AutoFiltersCollection filters = sheet.getAutoFilters();

        //设置筛选器的添加范围
        filters.setRange(sheet.getCellRange(1,1,sheet.getLastRow(),sheet.getLastColumn()));

        //添加筛选器
        int finalIndex = index-1;
        numbers.forEach(n-> filters.addFilter(finalIndex, n));

        //运行筛选功能
        List<Integer> rows = IntStream.rangeClosed(1, sheet.getLastRow())
                .boxed()
                .collect(Collectors.toList());

        List<Integer> deleteRows= Ints.asList(filters.filter(true));
        List<Integer> visibleRows= rows.stream().filter(i->!deleteRows.contains(i)).collect(Collectors.toList());
        Workbook wb1=new Workbook();
        Worksheet ws=wb1.getWorksheets().get(0);
        //依次复制筛选出的行到新的文件
        for (int i = 0; i < visibleRows.size(); i++) {
            CellRange range1 = sheet.getCellRange(visibleRows.get(i),1,visibleRows.get(i),sheet.getLastColumn());
            CellRange range2 = ws.getCellRange(i+1,1,i+1,sheet.getLastColumn());
            sheet.copy(range1,range2,true);
        }
        ws.setName(fileName.substring(0,fileName.indexOf(".")));
        //获取第一个工作表
        wb1.saveToFile(directoryOfFilted+fileName,ExcelVersion.Version2007);
        log.debug("用时{}秒",(System.currentTimeMillis()-startTime)/1000);
        log.debug("{}处理完毕",fileName);
        return directoryOfFilted+fileName;
    }
}
